package cdms.db.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import cdms.db.SenderRepository;
import cdms.domain.Sender;
import cdms.web.PaginationSupport;

/**
 * 
 * <p>
 * Project name:CDMS
 * </p>
 * <p>
 * Class name:JdbcSenderRepository
 * </p>
 * 
 * @author "JiHongru"
 * @version 1.0
 * @date 2019年11月17日下午1:55:16
 * @description Data access class implements SenderRepository Interface
 *
 */
@Repository
public class JdbcSenderRepository implements SenderRepository {

	@Autowired
	private JdbcTemplate jdbc;

	/**
	 * Get sender accounts Quantity
	 * 
	 * @return long
	 */
	public long count() {
		long count = -1;
		try {
			count = jdbc.queryForLong("select count(id) from sender where isdeleted = false");
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * count senders by username
	 * 
	 * @param username
	 * @return long
	 */
	public long countByUserName(String username) {
		long count = -1;
		username = "%" + username + "%";
		try {
			count = jdbc.queryForLong("select count(id) from sender where isdeleted = false and username like ?",
					username);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * count senders by fullname
	 * 
	 * @param fullname
	 * @return long
	 */
	public long countByFullName(String fullname) {
		long count = -1;
		fullname = "%" + fullname + "%";
		try {
			count = jdbc.queryForLong("select count(id) from sender where isdeleted = false and fullname like ?",
					fullname);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * Save a sender account
	 * 
	 * @return Sender
	 * @param Sender
	 */
	public Sender save(Sender sender) {
		Sender sender0 = null;
		try {
			if (sender.getId() == null) {
				jdbc.update(INSERT_SENDER, sender.getUserName(), sender.getPassword(), sender.getFullname(),
						sender.isDelete(), sender.getPhoneNo(), false);
			} else if (sender.getId() != null) {
				jdbc.update(UPDATE_SENDER, sender.getUserName(), sender.getPassword(), sender.getFullname(),
						sender.isDelete(), sender.getPhoneNo());
			}
			sender0 = findOneByUserName(sender.getUserName());
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		return sender0;
	}

	/**
	 * delete a sender by id
	 * 
	 * @param senderId
	 * @return boolean
	 */
	public boolean delete(long senderId) {
		try {
			jdbc.update(DELETE_SENDER, senderId);
			return true;
		} catch (DataAccessException e) {
			return false;
		}
	}

	/**
	 * update sender information
	 * 
	 * @param sender
	 * @return Sender
	 */
	public Sender update(Sender sender) {
		if (sender.getPassword() == null) {
			jdbc.update(UPDATE_SENDER, sender.getFullname(), sender.getPhoneNo(), sender.getUserName());
		} else {
			jdbc.update(UPDATE_PASSWORD, sender.getPassword(), sender.getUserName());
		}
		sender = findOneByUserName(sender.getUserName());
		return sender;
	}

	/**
	 * 
	 */
	public Sender updateUserNameFullNamePasswordPhoneNoById(Sender sender) {

		jdbc.update(UPDATE_ALL, sender.getUserName(), sender.getPassword(), sender.getFullname(), false, sender.getPhoneNo(), sender.getUserName());
		sender = findOneByUserName(sender.getUserName());
		return sender;

	}

	/**
	 * Get a sender account by id
	 * 
	 * @return Sender
	 * @param long
	 */
	public Sender findOneById(long id) {
		Sender sender = null;
		try {
			sender = jdbc.queryForObject(SELECT_SENDER + "and id = ?", new SenderRowMapper(), id);
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		return sender;
	}

	/**
	 * Get a sender account by username
	 * 
	 * @return Sender
	 * @param String
	 */
	public Sender findOneByUserName(String username) {
		Sender sender = null;
		try {
			sender = jdbc.queryForObject(SELECT_ALL + "where username = ?", new SenderRowMapper(), username);
		} catch (DataAccessException e) {
		}
		return sender;
	}

	/**
	 * Get a sender account by username and password
	 * 
	 * @return Sender
	 * @param String
	 * @param String
	 */
	public Sender findOneByUsernameAndPassWord(String username, String password) {
		Sender sender = null;
		try {
			sender = jdbc.queryForObject(SELECT_SENDER + "and username = ? and password = ?", new SenderRowMapper(),
					username, password);
		} catch (DataAccessException e) {
		}
		return sender;
	}

	/**
	 * Get all sender accounts
	 * 
	 * @return List<Sender>
	 */
	public List<Sender> findAll() {
		List<Sender> senders = new ArrayList<Sender>();
		try {
			senders = jdbc.query(SELECT_SENDER + "order by id", new SenderRowMapper());
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		return senders;
	}

	/**
	 * Get a page of senders
	 * 
	 * @return PaginationSupport<Sender>
	 */
	public PaginationSupport<Sender> findPage(int pageNo, int pageSize) {
		int totalCount = (int) count();
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1) {
			return new PaginationSupport<Sender>(new ArrayList<Sender>(0), 0);
		}
		List<Sender> senders = jdbc.query(SELECT_PAGE_SENDER, new SenderRowMapper(), pageSize, startIndex);
		PaginationSupport<Sender> ps = new PaginationSupport<Sender>(senders, totalCount, pageSize, startIndex);
		return ps;
	}

	/**
	 * Get a page of senders by username
	 * 
	 * @param int    pageNo
	 * @param int    pageSize
	 * @param String username
	 * @return PaginationSupport<Sender>
	 */
	public PaginationSupport<Sender> searchByUserName(int pageNo, int pageSize, String username) {
		int totalCount = (int) countByUserName(username);
		username = "%" + username + "%";
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1) {
			return new PaginationSupport<Sender>(new ArrayList<Sender>(0), 0);
		}
		List<Sender> senders = jdbc.query(SEARCHBYUSERNAME, new SenderRowMapper(), username, pageSize, startIndex);
		PaginationSupport<Sender> ps = new PaginationSupport<Sender>(senders, totalCount, pageSize, startIndex);
		return ps;
	}

	/**
	 * Get a page of senders by fullname
	 * 
	 * @param int    pageNo
	 * @param int    pageSize
	 * @param String fullname
	 * @return PaginationSupport<Sender>
	 */
	public PaginationSupport<Sender> searchByFullName(int pageNo, int pageSize, String fullname) {
		int totalCount = (int) countByFullName(fullname);
		fullname = "%" + fullname + "%";
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1) {
			return new PaginationSupport<Sender>(new ArrayList<Sender>(0), 0);
		}
		List<Sender> senders = jdbc.query(SEARCHBYFULLNAME, new SenderRowMapper(), fullname, pageSize, startIndex);
		PaginationSupport<Sender> ps = new PaginationSupport<Sender>(senders, totalCount);
		return ps;
	}

	/**
	 * <p>
	 * Class name:SenderRowMapper
	 * </p>
	 * 
	 * @description Sender RowMap
	 */
	private static class SenderRowMapper implements RowMapper<Sender> {

		@Override
		public Sender mapRow(ResultSet result, int rowNum) throws SQLException {
			return new Sender(result.getLong("id"), result.getString("username"), result.getString("password"),
					result.getString("fullname"), result.getBoolean("isdeleted"), result.getString("phoneno"), false);
		}

	}

	/**
	 * Get the number of online senders
	 */
	public int onlineCount() {
		int count = 0;
		if (jdbc != null)
			count = (int) jdbc.queryForLong("select count(id) from sender where isdeleted = false and isOnline = true");
		return count;
	}

	/**
	 * Update the sender online by userName
	 */
	public boolean updateOnline(boolean isOnline, String userName) {
		jdbc.update(UPDATE_ONLINE, isOnline, userName);
		return true;
	}

	/**
	 * PostgreSql insert phrases for sender
	 */
	private static final String INSERT_SENDER = "insert into sender(username, password, fullname, isdeleted, phoneno,isOnLine) values(?, ?, ?, ?, ?,?)";

	/**
	 * PostgreSql delete phrases for sender
	 */
	private static final String DELETE_SENDER = "update sender set isdeleted = true where id = ?";

	/**
	 * PostgreSql update phrases for sender
	 */
	private static final String UPDATE_SENDER = "update sender set fullname = ?, phoneno = ? where username = ?";

	/**
	 * update sender account password
	 */
	private static final String UPDATE_PASSWORD = "update sender set password = ? where username = ?";

	/**
	 * PostgreSql select phrases for sender
	 */
	private static final String SELECT_SENDER = "select * from sender where isdeleted = false ";

	private static final String SELECT_ALL = "select * from sender ";

	/**
	 * update all information of sender by id
	 */
	private static final String UPDATE_ALL = "update sender set username = ?, password = ?, fullname = ?, isdeleted = ?, phoneno = ? where username = ?";

	/**
	 * PostgreSql select page of senders phrases
	 */
	private static final String SELECT_PAGE_SENDER = SELECT_SENDER + "order by id limit ? offset ?";

	/**
	 * PostgreSql select page of senders by userName
	 */
	private static final String SEARCHBYUSERNAME = SELECT_SENDER + "and username like ? order by id limit ? offset ?";

	/**
	 * PostgreSql select page of senders by fullName
	 */
	private static final String SEARCHBYFULLNAME = SELECT_SENDER + "and fullname like ? order by id limit ? offset ?";

	/**
	 * PostgreSql update the sender online by userName
	 */
	private static final String UPDATE_ONLINE = "update sender set isOnline = ? where userName = ?";
}
